import csv
import os
import random
import time


import pysftp
import requests
import json
import uuid
from datetime import datetime, timedelta

from requests import session


# ======================
# 1. 创建CSV文件并赋值
# ======================
def create_csv():
    # 生成唯一文件名
    # filename = f"data_{datetime.now().strftime('%Y%m%d%H%M%S')}.csv"
    num = random.randint(0, 999999)
    orderId = f"test{num:06d}"
    print("订单号：" + orderId)
    orderTime = '2025-05-13 21:05:00'
    memberId = 'laprairie_POS_8000420577'
    refundId = 'test217351'

    # 定义数据
    data = [
        ["brandCode", "orderChannel", "storeCode", "storeName", "orderCode", "orderType", "originalOrderCode",
         "orderTime", "deliveryTime", "BCCode", "originalAmount", "realAmount", "quantity", "marsId",
         "originalUpdateTime", "rn"],
        # ["LP", "POS", "0130", "广州天环", orderId, "PURCHASE", refundId, orderTime, "", "1376", "0.0", "0.0", "2",
        #  memberId, orderTime, "1"],
        # ["LP", "JD", "0058", "JD自营", orderId, "PURCHASE", refundId, orderTime, "", "", "850.0", "850.0",
        #  "2", memberId, orderTime, "1"],
        # ["LP", "TMALL", "0089", "Tmall China LP", "3897259849505960329", "PURCHASE", "", "2024-05-21 00:13:27", "", "",
        #  "3450.0", "3450.0", "9", "0860611012", "2025-05-16 04:01:05.350000000", "1"],
        # ["LP", "DY", "0188", "DY自营", "K240914001937", "PURCHASE", "", "2024-09-14 13:46:43", "", "", "1980.0",
        #  "1980.0", "6", "4100037726", "2025-05-16 04:01:28.350000000", "1"],
        # ["LP", "MINI_PROGRAMS", "0091", "wechat mini programs", "PRM2505145154002", "PURCHASE", "",
        #  "2025-05-15 00:00:01", "", "", "10570.0", "10570.0", "12", "9100035723", "2025-05-15 00:00:01", "1"]
        ["LP", "POS", "0130", "广州天环", "test202506180001", "PURCHASE", refundId, "2025-08-01 21:05:00", "", "1376", "0.0", "0.0", "2",
         "7100000154", orderTime, "1"],
        ["LP", "POS", "0130", "广州天环", "test202506180002", "PURCHASE", refundId, "2025-08-02 21:05:00", "", "1376",
         "0.0", "0.0", "2",
         "7100000154", orderTime, "1"],
        ["LP", "POS", "0130", "广州天环", "test202506180003", "PURCHASE", refundId, "2025-08-03 21:05:00", "", "1376",
         "0.0", "0.0", "1",
         "7100000154", orderTime, "1"],
        ["LP", "POS", "0130", "广州天环", "test202506180004", "PURCHASE", refundId, "2025-08-04 21:05:00", "", "1376",
         "0.0", "0.0", "1",
         "7100000154", orderTime, "1"],
        ["LP", "POS", "0130", "广州天环", "test202506180005", "PURCHASE", refundId, "2025-08-05 21:05:00", "", "1376",
         "0.0", "0.0", "1",
         "7100000154", orderTime, "1"],
        ["LP", "POS", "0130", "广州天环", "test202506180006", "PURCHASE", refundId, "2025-08-03 21:05:00", "", "1376",
         "0.0", "0.0", "1",
         "7100000154", orderTime, "1"],
        ["LP", "POS", "0130", "广州天环", "test202506180007", "PURCHASE", refundId, "2025-08-04 21:05:00", "", "1376",
         "0.0", "0.0", "1",
         "7100000154", orderTime, "1"],
        ["LP", "POS", "0130", "广州天环", "test202506180008", "PURCHASE", refundId, "2025-08-05 21:05:00", "", "1376",
         "0.0", "0.0", "1",
         "7100000154", orderTime, "1"],
        ["LP", "POS", "0130", "广州天环", "test202506180009", "PURCHASE", refundId, "2025-08-05 21:05:00", "", "1376",
         "0.0", "0.0", "1",
         "7100000152", orderTime, "1"],
        ["LP", "POS", "0130", "广州天环", "test2025061800010", "PURCHASE", refundId, "2025-08-05 21:05:00", "", "1376",
         "0.0", "0.0", "2",
         "7100000155", orderTime, "1"],
        ["LP", "POS", "0130", "广州天环", "test2025061800011", "PURCHASE", refundId, "2025-08-05 21:05:00", "", "1376",
         "0.0", "0.0", "2",
         "1100000145", orderTime, "1"],
        ["LP", "POS", "0130", "广州天环", "test2025061800012", "PURCHASE", refundId, "2025-08-01 21:05:00", "", "1376",
         "0.0", "0.0", "2",
         "7100000153", orderTime, "1"],
        ["LP", "POS", "0130", "广州天环", "test2025061800013", "PURCHASE", refundId, "2025-08-02 21:05:00", "", "1376",
         "0.0", "0.0", "2",
         "7100000153", orderTime, "1"],
        ["LP", "POS", "0130", "广州天环", "test2025061800014", "PURCHASE", refundId, "2025-08-03 21:05:00", "", "1376",
         "0.0", "0.0", "2",
         "7100000153", orderTime, "1"]
    ]

    data2 = [
        [
            "orderCode", "itemSeqNo", "productCode", "originalPrice", "price",
            "originalAmount", "realAmount", "qty", "BCCode", "BCName",
            "productName", "productEnName"
        ],
        # [orderId, "1", "51530032", "1980.0", "1980.0", "1980.0", "1980.0", "1", "", "",
        #  "鱼子精华气垫粉底  粉嫩象牙色", "SC EIF 2X15MLTDR IVORY NW-10"],
        # [orderId, "2", "51530036", "1980.0", "1980.0", "1980.0", "1980.0", "1", "", "",
        #  "鱼子精华气垫粉底  娇嫩花瓣色", "SC EIF 2X15MLPETALE NC-05"]
        ["test202506180001", "1", "51530032", "6800.0", "6800.0", "6800.0", "6800.0", "1", "", "",
         "鱼子精华气垫粉底  粉嫩象牙色", "SC EIF 2X15MLTDR IVORY NW-10"],
        ["test202506180001", "2", "51530036", "6800.0", "6800.0", "6800.0", "6800.0", "1", "", "",
         "鱼子精华气垫粉底  娇嫩花瓣色", "SC EIF 2X15MLPETALE NC-05"],
        ["test202506180002", "1", "51530032", "6800.0", "6800.0", "6800.0", "6800.0", "1", "", "",
         "鱼子精华气垫粉底  粉嫩象牙色", "SC EIF 2X15MLTDR IVORY NW-10"],
        ["test202506180002", "2", "51530036", "6800.0", "6800.0", "6800.0", "6800.0", "1", "", "",
         "鱼子精华气垫粉底  娇嫩花瓣色", "SC EIF 2X15MLPETALE NC-05"],
        ["test202506180003", "1", "51530032", "6800.0", "6800.0", "6800.0", "6800.0", "1", "", "",
         "鱼子精华气垫粉底  粉嫩象牙色", "SC EIF 2X15MLTDR IVORY NW-10"],
        ["test202506180004", "1", "51530032", "6800.0", "6800.0", "6800.0", "6800.0", "1", "", "",
         "鱼子精华气垫粉底  粉嫩象牙色", "SC EIF 2X15MLTDR IVORY NW-10"],
        ["test202506180005", "1", "51530032", "6800.0", "6800.0", "6800.0", "6800.0", "1", "", "",
         "鱼子精华气垫粉底  粉嫩象牙色", "SC EIF 2X15MLTDR IVORY NW-10"],
        ["test202506180006", "1", "51530032", "6800.0", "6800.0", "6800.0", "6800.0", "1", "", "",
         "鱼子精华气垫粉底  粉嫩象牙色", "SC EIF 2X15MLTDR IVORY NW-10"],
        ["test202506180007", "1", "51530032", "6800.0", "6800.0", "6800.0", "6800.0", "1", "", "",
         "鱼子精华气垫粉底  粉嫩象牙色", "SC EIF 2X15MLTDR IVORY NW-10"],
        ["test202506180008", "1", "51530032", "6800.0", "6800.0", "6800.0", "6800.0", "1", "", "",
         "鱼子精华气垫粉底  粉嫩象牙色", "SC EIF 2X15MLTDR IVORY NW-10"],
        ["test202506180009", "1", "51530032", "6800.0", "6800.0", "6800.0", "6800.0", "1", "", "",
         "鱼子精华气垫粉底  粉嫩象牙色", "SC EIF 2X15MLTDR IVORY NW-10"],
        ["test2025061800010", "1", "51530032", "1000.0", "1000.0", "1000.0", "1000.0", "1", "", "",
         "鱼子精华气垫粉底  粉嫩象牙色", "SC EIF 2X15MLTDR IVORY NW-10"],
        ["test2025061800010", "2", "51530036", "1000.0", "1000.0", "1000.0", "1000.0", "1", "", "",
         "鱼子精华气垫粉底  娇嫩花瓣色", "SC EIF 2X15MLPETALE NC-05"],
        ["test2025061800011", "1", "51530032", "68000.0", "68000.0", "68000.0", "68000.0", "1", "", "",
         "鱼子精华气垫粉底  粉嫩象牙色", "SC EIF 2X15MLTDR IVORY NW-10"],
        ["test2025061800011", "2", "51530036", "68000.0", "68000.0", "68000.0", "68000.0", "1", "", "",
         "鱼子精华气垫粉底  娇嫩花瓣色", "SC EIF 2X15MLPETALE NC-05"],
        ["test2025061800012", "1", "51530032", "6800.0", "6800.0", "6800.0", "6800.0", "1", "", "",
         "鱼子精华气垫粉底  粉嫩象牙色", "SC EIF 2X15MLTDR IVORY NW-10"],
        ["test2025061800012", "2", "51530036", "6800.0", "6800.0", "6800.0", "6800.0", "1", "", "",
         "鱼子精华气垫粉底  娇嫩花瓣色", "SC EIF 2X15MLPETALE NC-05"],
        ["test2025061800013", "1", "51530032", "6800.0", "6800.0", "6800.0", "6800.0", "1", "", "",
         "鱼子精华气垫粉底  粉嫩象牙色", "SC EIF 2X15MLTDR IVORY NW-10"],
        ["test2025061800013", "2", "51530036", "6800.0", "6800.0", "6800.0", "6800.0", "1", "", "",
         "鱼子精华气垫粉底  娇嫩花瓣色", "SC EIF 2X15MLPETALE NC-05"],
        ["test2025061800014", "1", "51530032", "68000.0", "68000.0", "68000.0", "68000.0", "1", "", "",
         "鱼子精华气垫粉底  粉嫩象牙色", "SC EIF 2X15MLTDR IVORY NW-10"],
        ["test2025061800014", "2", "51530036", "6800.0", "68000.0", "68000.0", "68000.0", "1", "", "",
         "鱼子精华气垫粉底  娇嫩花瓣色", "SC EIF 2X15MLPETALE NC-05"],
    ]

    time = (datetime.now() - timedelta(days=1)).strftime('%Y_%m_%d')
    # 创建文件名（带时间戳）
    filename = "LP_Order_2000_02_02.csv"
    fileitemname = "LP_Order_Item_2000_02_02.csv"

    # 写入CSV文件（使用分号分隔符）
    write_csv(filename, data)
    write_csv(fileitemname, data2)

    print(f"CSV头文件已创建: {filename}")
    print(f"CSV明细文件已创建: {fileitemname}")
    print(f"CSV头文件共写入 {len(data) - 1} 条订单记录")
    print(f"CSV明细文件共写入 {len(data2) - 1} 条订单记录")
    print("订单号：" + orderId)

    return filename + fileitemname


# 文件写入
def write_csv(filename, data, delimiter=';'):
    with open(filename, 'w', newline='', encoding='utf-8') as file:
        csv.writer(file, delimiter=delimiter).writerows(data)


# ======================
# 2. 上传文件到SFTP
# ======================
def upload_to_sftp():
    # SFTP连接配置 - 请替换为实际值
    sftp_config = {
        "host": "laprairie.ftpstream.com",
        "port": 22,
        "username": "lianwei",  # 格式: <容器名>.<用户名>
        "password": "ZdSJZMp4j6aP6Sr!",  # 或使用私钥
        "remote_path": "/test/order"  # SFTP目标路径
    }

    # 创建不验证主机密钥的连接选项
    cnopts = pysftp.CnOpts()
    cnopts.hostkeys = None  # 禁用主机密钥验证

    try:
        # 连接SFTP (使用密钥示例: pysftp.Connection(..., private_key="path/to/key"))
        with pysftp.Connection(
                host=sftp_config["host"],
                port=sftp_config["port"],
                username=sftp_config["username"],
                password=sftp_config["password"],
                cnopts=cnopts  # 使用自定义连接选项
        ) as sftp:
            #删除指定备份文件
            delete_file(sftp)
            # 切换到远程目录
            sftp.chdir(sftp_config["remote_path"])
            # 上传文件
            sftp.put("LP_Order_2000_02_02.csv")
            remote_file = f"{sftp_config['remote_path']}/{os.path.basename('LP_Order_2000_02_02.csv')}"
            sftp.put("LP_Order_Item_2000_02_02.csv")
            remote_files = f"{sftp_config['remote_path']}/{os.path.basename('LP_Order_Item_2000_02_02.csv')}"
            print(f"文件已上传至SFTP: {remote_file}")
            print(f"文件已上传至SFTP: {remote_files}")
            return remote_file
    except Exception as e:
        print(f"SFTP上传失败: {str(e)}")
        return None


# ======================
# 3. 删除指定文件
# ======================
def delete_file(sftp):
    # 切换到远程目录
    sftp.chdir("/test/bk")

    file_pattern = "*2000_02_02.csv"

    # 查找匹配的文件
    matching_files = [f for f in sftp.listdir() if f.endswith("2000_02_02.csv")]

    if not matching_files:
        print(f"未找到匹配 '{file_pattern}' 的文件")
        return

    print(f"找到 {len(matching_files)} 个匹配 '{file_pattern}' 的文件:")

    # 处理每个文件
    deleted_count = 0
    for file_path in matching_files:
        try:
            dry_run = False  # 设置为 False 以启用实际删除
            if dry_run:
                print(f"[模拟] 将删除: {file_path}")
            else:
                sftp.remove(file_path)
                print(f"已删除: {file_path}")
                deleted_count += 1
        except Exception as e:
            print(f"删除失败 '{file_path}': {str(e)}")

# ======================
# 4. 调用API解析文件
# ======================
def call_parse_api():
    # API配置 - 请替换为实际API端点
    api_config = {
        "url": "https://lployalty-dev.lianwei.com.cn/api/laprairie-portal/order/testOrderOne",
        "headers": {"Content-Type": "application/json"}
    }

    # ip = "http://localhost:56751"
    ip = "https://lployalty-dev.lianwei.com.cn"

    sess = requests.session()

    try:
        response = sess.request(method="get", url=
        f"{ip}/api/crm/order/processCsvForOrder",
                                headers=api_config["headers"],
                                )
        print(f"解析成功1{response.status_code}")

        response1 = sess.request(method="get", url=
        f"{ip}/api/crm/order/orderAllInfoConvert",
                                 headers=api_config["headers"],
                                 )

        print(f"解析成功2{response1.status_code}")

        response2 = sess.request(method="get", url=
        f"{ip}/api/crm/order/pushByTableNameWithMQ",
                                 headers=api_config["headers"],
                                 )
        print("推送")

        if response.status_code == 200 and response1.status_code == 200 and response2.status_code == 200:
            result = response.json()
            print("API调用成功")
            return result.get("generated_script")
        else:
            print(f"API错误: {response.status_code} - {response.text}")
            return None

    except Exception as e:
        print(f"API调用失败: {str(e)}")
        return None


# ======================
# 5. 主执行流程
# ======================
if __name__ == "__main__":
    # 步骤1: 创建CSV
    csv_file = create_csv()
    #
    # # 步骤2:删除指定文件（因存在备份文件，且备份文件相同会报错，需要提前删除）
    # # 步骤3: 上传到SFTP
    remote_path = upload_to_sftp()

    # 步骤4: 调用解析API
    call_parse_api()


